import pymysql
import wave
import oprt2wav


class Operate2Sql:
    def __init__(self, host, admin, password, database):
        self.host = host
        self.admin = admin
        self.password = password
        self.database = database

        self.lgstate = 0
        self.datatables = []
        self.datatable = ''

    def link2sql(self):
        config = {"host": self.host,
                  "user": self.admin,
                  "password": self.password,
                  "charset": "utf8",
                  }
        try:
            self.conn = pymysql.connect(**config)
            self.cur = self.conn.cursor()
            self.cur.execute("USE {0}".format(self.database))
            self.lgstate = 1
            self.findtable()
            return 1, "登陆成功！"
        except Exception as e:
            return 0, e.args[1]

    def findtable(self):
        if self.lgstate:
            self.cur.execute("SHOW TABLES")
            datatables = self.cur.fetchall()
            if datatables is not ():
                for datatable in datatables:
                    self.datatables.append(datatable[0])
            if len(self.datatables) == 1:
                self.datatable = self.datatables[0]
            # print(self.datatables)

    def settable(self, tablename):
        self.datatable = tablename

    def gettabletypes(self):
        sentence = "DESCRIBE {0}".format(self.datatable)
        typenames = ["数据名", "数据类型", "是否可以为空", "是否为主键", "默认值", "其它"]
        types = []
        try:
            if self.lgstate:
                self.cur.execute(sentence)
            for line in self.cur.fetchall():
                typer = {}
                for name, value in zip(typenames, line):
                    typer.update({name: value})
                types.append(typer)
            return typenames, types
        except Exception as e:
            print(e)
            return typenames, []

    def gettabletitles(self, datatable=''):
        if not datatable:
            datatable = self.datatable
        sentence = "DESCRIBE {0}".format(datatable)
        titles = []
        try:
            if self.lgstate:
                self.cur.execute(sentence)
            for line in self.cur.fetchall():
                titles.append(line[0])
            return titles
        except Exception as e:
            print(e)
            return []

    def gettableinfos(self, tablename=""):
        if tablename == "":
            tablename = self.datatable
        if self.datatable == "":
            return 0, "未设置数据表单"
        try:
            titles = self.gettabletitles(tablename)
            self.cur.execute("SELECT * FROM {}".format(tablename))
            infos = []
            for lines in self.cur.fetchall():
                info = {}
                for line, title in zip(lines, titles):
                    line = line
                    info.update({title: line})
                infos.append(info)
            return 1, infos
        except Exception as e:
            return 0, e.args[1]

    def getdata(self, title, name, datatable=""):
        if datatable == "":
            datatable = self.datatable
        if self.datatable == "":
            return 0, "未设置数据表单"

        sentence = "SELECT * FROM {0}".format(datatable)
        titles = self.gettabletitles(datatable)
        have_seek = 0
        try:
            self.cur.execute(sentence)
            for line in self.cur.fetchall():
                info = {}
                for title_, value in zip(tuple(titles), line):
                    info.update({title_: value})
                    if title_ == title and value == name:
                        have_seek = 1
                if have_seek:
                    return 1, info
            return 0, {}
        except Exception as e:
            print(e)
            return 0, {}
        pass

    def insert2sql(self, info):
        try:
            info_sentence = ''
            for value in info:
                try:
                    value = eval(value)
                    if isinstance(value, list) or isinstance(value, tuple):
                        value = "{}".format(value)
                except:
                    value = value
                if isinstance(value, str):
                    info_sentence += ("'{0}'".format(value) + ', ')
                elif isinstance(value, int) or isinstance(value, float):
                    info_sentence += (str(value) + ', ')
            # print(info_sentence)
            info_sentence = info_sentence[:-2]
            self.cur.execute('''INSERT INTO {0} VALUES({1})'''.format(self.datatable, info_sentence))
            self.conn.commit()
            return 1, "导入数据成功"
        except Exception as e:
            print(e)
            if e.args[0] == 1136:
                return 0, "输入数据行数与数据库表的行数不同"
            elif e.args[0] == 1062:
                return 0, "输入数据Key值占用"
            return 0, e[1]


if __name__ == "__main__":
    sqlp = Operate2Sql("localhost", "root", "123456", "wavdata")
    sqlp.settable("wavtable")

    wavp = oprt2wav.ProcessAudio()
    is_link, link_info = sqlp.link2sql()
    if is_link == 1:
        # # 打印表头信息
        # print(sqlp.gettabletypes())

        # # 打印表头
        # print(sqlp.gettabletitles())

        # 输出数据表数据，并转为ave_read类
        _, infos = sqlp.gettableinfos()

        wavdata = infos[1]["wavdata"]
        f_reader = wave.convertbytes2wavereader(wavdata)

        # # 播放wav
        # wavp.playwav(f_reader)

        # # 画wav波形图
        # data = wavp.readwav(f_reader)
        # wavp.plotwav(data, 16000, len(data))

        # # 画wav语图
        # wavp.plotspect(f_reader)

        # 画wav频谱图
        wavp.plot_spect(f_reader)
        pass
    pass

